﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PermitsAndVisasLeadsSyncher.Helpers
{
    static class QueryProvider
    {
        public static string MakeSqlQuerytoReturnProductInfo()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT prod.Product_ID,prod.Product_Name,ptype.ProductType_Name, prod.Product_DestinationID, ");
            stringBuilder.Append("ptype.ProductType_ResidenceID from L_Products as Prod join L_ProductTypes as ptype");
            stringBuilder.Append(" on prod.Product_Type=ptype.ProductType_ID");
            return stringBuilder.ToString();
        }

        public static string MakeSqlQueryToFetchLastSucessfullLeadSynched()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("declare  @LastId int ");
            stringBuilder.Append("set @LastId =(select MAX(Id) FROM [L_LeadsSyncInfo]  where [LastSuccessfullLeadDate] not like '' ) ");
            stringBuilder.Append("SELECT [LastSuccessfullLeadDate] FROM [L_LeadsSyncInfo] where ID= @LastId  ");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddLastSuccessfullLeadsDate(string LastSucessfullEdate)
        {
            string Query = string.Format("INSERT INTO [L_LeadsSyncInfo] ([LastSuccessfullLeadDate],[LeadSyncTime]) values ('{0}','{1}')", LastSucessfullEdate, DateTime.Now);
            return Query;
        }

        public static string MakeSqlQueryToGetFirstLeadInfoCheckValue()
        {
            return "SELECT MAX(Id) FROM [L_LeadsSyncInfo]";
        }

        public static string MakeSQLQueryToAddNewEventOfAddingPerson(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID,");
            stringBuilder.Append("Event_LinkType, Event_Keydate, Event_UserEvent)");
            stringBuilder.AppendFormat(" VALUES ( 'Person Added', '{0}', 0, '{1}', 1, '{2}', 2 , 0,0 ) ", Leads.Name, Leads.EnteredDate, Leads.PersonID);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddNewEventOfAddingCase(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID,");
            stringBuilder.Append("Event_LinkType, Event_Keydate, Event_UserEvent)");
            stringBuilder.AppendFormat(" VALUES ( 'Case Added', '{0}', 0, '{1}', 1, '{2}', 1 , 0,0 ) ", Leads.Name, Leads.EnteredDate, Leads.CaseId);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddNewCase(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("Insert Into D_Case (Case_DtEntered,Case_EnteredBy, Case_ProductID, Case_ConSaleID, Case_ConCWID ");
            stringBuilder.Append(" , Case_CompanyID, Case_StatusID, Case_DTStatus, Case_DTsold, Case_CandidateID, Case_ContactID, Case_PrimaryCaseID, Case_CoordinatorID)");
            stringBuilder.AppendLine(" OUTPUT INSERTED.Case_ID ");
            stringBuilder.AppendFormat(" values ('{0}','LeadSyncher','{1}',0,0,0,1,'{0}','{0}','{2}',0,0,0)", Leads.EnteredDate, Leads.ProductId, Leads.PersonID);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToFetchEducationList()
        {
            return "SELECT [Qual_Name] FROM [L_Qualifications]";
        }

        public static string MakeSQLQueryToFetchCountryList()
        {
            return "SELECT [Country_ID],[Country_Name],[Country_Nationality]  FROM [L_Country]";
        }

        public static string MakeSQLQueryToAddMobileNumber(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into [D_PhoneNumber] ( [PhoneNumber_Name] ");
            stringBuilder.Append(",[PhoneNumber_Number],[PhoneNumber_Type] ,[PhoneNumber_PersonID],[PhoneNumber_DTEntered]");
            stringBuilder.Append(",[PhoneNumber_EnteredBy] ) ");
            stringBuilder.AppendFormat("values ('Mobile', '{0}', 1 ,{1}, '{2}' ,'' ) ", Leads.MobileNumber, Leads.PersonID, Leads.EnteredDate);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddPhoneNumber(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into [D_PhoneNumber] ( [PhoneNumber_Name] ");
            stringBuilder.Append(",[PhoneNumber_Number],[PhoneNumber_Type] ,[PhoneNumber_PersonID],[PhoneNumber_DTEntered]");
            stringBuilder.Append(",[PhoneNumber_EnteredBy] ) ");
            stringBuilder.AppendFormat("values ('Phone', '{0}', 1 ,{1}, '{2}' ,'' ) ", Leads.PhoneNumber, Leads.PersonID, Leads.EnteredDate);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddEmailAddress(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into [D_EmailAddress] ( [EMailAddress_Name] ");
            stringBuilder.Append(",[EMailAddress_Address],[EMailAddress_PersonID] ,[EMailAddress_Type],[EmailAddress_DTEntered]");
            stringBuilder.Append(",[EMailAddress_EnteredBy] ) ");
            stringBuilder.AppendFormat("values ('Email', '{0}',{1},1,'{2}' ,'' ) ", (object)Leads.Email, Leads.PersonID, Leads.EnteredDate);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddWebsiteComment(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID, ");
            stringBuilder.Append(" Event_LinkType, Event_Keydate, Event_UserEvent)");
            stringBuilder.AppendFormat("VALUES ( 'Website Comments', '{0}', 0, '{1}', 1, '{2}', 1 , 0,1 )", Leads.Message, Leads.EnteredDate, Leads.CaseId);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToAddPerson(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into [D_Person] ( [Person_FName] ");
            stringBuilder.Append(",[Person_EnteredBy],[Person_DTEntered] ,[Person_Lname],[Person_Title]");
            stringBuilder.Append(",[Person_NatID],[Person_ResidentID],[Person_UKVisaID],[Person_DestinationID] ");
            stringBuilder.Append(",[Person_Jobtitle],[Person_salary],[Person_Qualification],[Person_PassportNo] ");
            stringBuilder.Append(",[person_dob],[person_expirydt],[person_PassportDTexpiry],[Person_CompanyID]");
            stringBuilder.Append(",[Person_CompanyRoleID],[Person_AccountManager])");
            stringBuilder.AppendLine(" OUTPUT INSERTED.Person_ID ");
            stringBuilder.AppendFormat(" values ('{0}',0,'{1}','','','{2}','{3}',8,'{4}", Leads.Name, Leads.EnteredDate,Leads.NationalityID, Leads.ResidenceCountryId, Leads.DestinationCountryId);
            stringBuilder.AppendFormat("','{0}','','{1}','',null,null,null,0,0,0)", Leads.Occupation, Leads.Education);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToUpdateEducation(PermitsAndVisasLeadsSyncher.Model.Lead Leads)
        {
            string str = string.Format("insert into [L_Qualifications] ([Qual_Name] ) values ('{0}')", Leads.Education);
            return str;
        }
    }
}
